﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;

namespace DAL
{
    public class RawsInfoServices
    {
        //查询所有
        public static List<RawsInfo> Select()
        {
            string sql = "select * from Raws";
            DataTable dt = DBhelper.SqlDatatable(sql);
            List<RawsInfo> raws = new List<RawsInfo>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                RawsInfo r = new RawsInfo
                {
                    rawId = Convert.ToInt32(dt.Rows[i]["rawId"]),
                    RawMaterial = dt.Rows[i]["RawMaterial"].ToString(),
                    RawsAddress = dt.Rows[i]["RawsAddress"].ToString(),
                    Supplier = dt.Rows[i]["Supplier"].ToString(),
                    Unitprice = Convert.ToDouble(dt.Rows[i]["Unitprice"].ToString()),
                    unit = dt.Rows[i]["unit"].ToString(),
                    Introduce = dt.Rows[i]["Introduce"].ToString(),
                    Remark = dt.Rows[i]["Remark"].ToString(),
                    states = dt.Rows[i]["states"].ToString(),
                    people = dt.Rows[i]["people"].ToString(),
                    times = Convert.ToDateTime(dt.Rows[i]["times"].ToString())
                };
                raws.Add(r);
            }
            return raws;
        }
        //通过条件查询
        public static List<RawsInfo> AllSelect(string name,string address,string supplier)
        {
            string sqlbyName = "", sqlbyAddress = "", sqlbySupplier = "";
            if (name!=string.Empty)
            {
                sqlbyName = "RawMaterial like '%" + name + "%'";
            }
            if (address != string.Empty)
            {
                sqlbyAddress = "RawsAddress like '%" + address + "%'";
            }
            if (supplier != string.Empty)
            {
                sqlbySupplier = "Supplier like '%" + supplier + "%'";
            }
            string sql = "select * from Raws where " + sqlbyName + sqlbyAddress + sqlbySupplier;
            DataTable dt = DBhelper.SqlDatatable(sql);
            List<RawsInfo> raws = new List<RawsInfo>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                RawsInfo r = new RawsInfo
                {
                    rawId = Convert.ToInt32(dt.Rows[i]["rawId"]),
                    RawMaterial = dt.Rows[i]["RawMaterial"].ToString(),
                    RawsAddress = dt.Rows[i]["RawsAddress"].ToString(),
                    Supplier = dt.Rows[i]["Supplier"].ToString(),
                    Unitprice = Convert.ToDouble(dt.Rows[i]["Unitprice"].ToString()),
                    unit = dt.Rows[i]["unit"].ToString(),
                    Introduce = dt.Rows[i]["Introduce"].ToString(),
                    Remark = dt.Rows[i]["Remark"].ToString(),
                    states = dt.Rows[i]["states"].ToString(),
                    people = dt.Rows[i]["people"].ToString(),
                    times = Convert.ToDateTime(dt.Rows[i]["times"].ToString())
                };
                raws.Add(r);
            }
            return raws;
        }
        //新增
        public static RawsInfo AddInfo(string RawMaterial, string RawsAddress, string Supplier, double Unitprice, string unit, string Introduce, string Remark, string states, string people, DateTime times)
        {
            string sql = string.Format("insert into Raws values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",RawMaterial, RawsAddress, Supplier, Unitprice, unit, Introduce, Remark, states, people, times);
            bool result = DBhelper.ExecuteNonQuery(sql);
            RawsInfo r = new RawsInfo();
            if (result)
            {
                r.RawMaterial = RawMaterial;
                r.RawsAddress = RawsAddress;
                r.Supplier = Supplier;
                r.Unitprice = Unitprice;
                r.unit = unit;
                r.Introduce = Introduce;
                r.Remark = Remark;
                r.states = states;
                r.people = people;
                r.times = times;
            }
            else
            {
                r = null;
            }
            return r;
        }
    }
}
